package com.ruoyi.factory.taskShow.controller.system;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


public class OraclTest {

    public static void main(String[] args) {

        // 定义oracle数据库的驱动的类
        String driverClass = "oracle.jdbc.OracleDriver";
        // 定义连接的oracle的url
        String url = "jdbc:oracle:thin:@61.163.50.132:1521:orcl";
        // 用户名
        String username = "sj0731";
        // 密码
        String password = "sj0731";

        // 定义Connection连接
        Connection conn = null;
        // 定义数据库的sql执行对象
        Statement stmt = null;

        // 定义查询结果的ResultSet对象
        ResultSet rs = null;

        //定义sql插入语句
        //String addsql = "insert into person(id,name,gender,birthday)values(personid.nextval,'李四','1',to_date('1996-09-23','yyyy-mm-dd'))";

        //定义sql 查询语句
        String querysql = "select pk_group,csaleorgid,cinventoryid,caccountperiod,nprice from (select tf.*,ROW_NUMBER() OVER(PARTITION BY  csaleorgid,cinventoryid ORDER BY caccountperiod DESC , pk_org ) RN from ia_i5bill_b tf where nprice is not null )  test where test .rn = 1 ORDER BY caccountperiod desc";

        try {
            // 注册数据库的驱动程序
            Class.forName(driverClass);
            DriverManager.setLoginTimeout(60);
            // 获得数据库的连接
            conn = DriverManager.getConnection(url, username, password);
            // 创建sql执行对象
            stmt = conn.createStatement();
            // 执行查询
            rs = stmt.executeQuery(querysql);

            //通过列名来获取数据
            while (rs.next()) {
                String id = rs.getString("pk_group");
                String name = rs.getString("csaleorgid");
                String gender = rs.getString("cinventoryid");
                String birthday = rs.getString("caccountperiod");
                String nprice = rs.getString("nprice");
                System.out.println("人员编号：" + id + "     姓名：" + name + "       性别：" + gender + "       生日：" + birthday);
            }

            //通过列名序号来获取数据
      /*
      while (rs.next())
      {
        String id = rs.getString(1);
        String name = rs.getString(2);
        String gender = rs.getString(3);
        String birthday = rs.getString(4);
        System.out.println("人员编号：" + id + "     姓名：" + name+ "       性别：" + gender + "       生日：" + birthday);
      }
      */

            //通过列名来获取数据（数据类型转换）
      /*
      while (rs.next())
      {
        Integer id = rs.getInt("id");
        String name = rs.getString("name");
        String gender = rs.getString("gender");
        Date birthday = rs.getDate("birthday");
        System.out.println("人员编号：" + id + "     姓名：" + name + "       地址：" + gender + "       生日：" + birthday);
      }
      */

            //通过列名来获取数据（使用 父类 object）
      /*
      while (rs.next())
      {
        Object id = rs.getObject("id");
        Object name = rs.getObject("name");
        Object gender = rs.getObject("gender");
        Object birthday = rs.getObject("birthday");
        System.out.println("人员编号：" + id + "     姓名：" + name+ "       地址：" + gender + "       生日：" + birthday);
      }
      */

            // 获得当前表的元数据：表名，列名，列的数量
            ResultSetMetaData rsmd = rs.getMetaData();
            // 获得列的数量
            Integer cols = rsmd.getColumnCount();
            System.out.println("列数：" + cols);

            List<String> listColum = new ArrayList<String>();
            for (int i = 1; i <= 4; i++)
            {
                // 获得列名
                String columnName = rsmd.getColumnName(i);
                listColum.add(columnName);
            }
            // 根据获得的列名的元数据来获得该列名下的值
            while (rs.next())
            {
                for (String cname : listColum)
                {
                    String val = rs.getString(cname);
                    System.out.print(cname + ":  " + val + "    ");
                }
                System.out.println();
            }

        } catch (Exception e)
        {
            e.printStackTrace();
        } finally
        {
            try
            {
                if (rs != null)
                {
                    rs.close();
                }
                if (stmt != null)
                {
                    stmt.close();
                }
                if (conn != null)
                {
                    conn.close();
                }
            } catch (Exception e)
            {
                e.printStackTrace();
            }
        }
    }

}
